﻿using AchieveCommon;
using AchieveDALFactory;
using AchieveEntity;
using AchieveInterfaceDAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SqlClient;

namespace AchieveBLL
{
    /// <summary>
    /// 生产任务的业务逻辑层
    /// </summary>
    public class manufactureBLL
    {
        IProduceDAL dal = DALFactory.GetProduceDAL();
        public DataTable GetAllProduceData(string strwhere)
        {
            return dal.GetAllProduceData(strwhere);
        }
        /// <summary>
        /// 查询生产任务计划达成率，icmo表，默认年份2020,返回0-12行数据，0表示全年截至当前日期，其余为月份；
        /// </summary>
        public static DataTable getFBillFinishRate(string BillType,int yearID=2020){
            DataTable dt = new DataTable();
            String sql = @"
DECLARE @tb1 Table
(
Id int,
finished int,
billcount int,
rate float,
d1 datetime,
d2 datetime 
)
declare @f int 
declare @c int 
declare @i int
declare @d datetime
declare @d1 datetime
declare @d2 datetime
set @d='{0}-1-1' 
set @i=0
while @i<13 
   begin
 select @f=count(*) from icmo where
FPlanFinishDate between @d  and 
case @i when 0 then GETDATE() else dateadd(m,1,@d) end
and FFinishDate <= FPlanFinishDate
and FworktypeID not in (68);
select @c=count(*)  from icmo where
FPlanFinishDate between  @d and 
--dateadd(m,1,@d)
case @i when 0 then GETDATE() else dateadd(m,1,@d) end
and FworktypeID not in (68) 
insert into @tb1 (Id,finished,billcount,rate,d1,d2) values(
@i,@f,@c, isnull(@f*1.0/nullif(@c,0),0),@d,case @i when 0 then GETDATE() else dateadd(m,1,@d) end
)
if(@i>0)
    set @d= dateadd(m,1,@d)
set @i=@i+1
end 
select * from @tb1
";
            sql= string.Format(sql, yearID);
            dt = SqlHelper.GetDataTableK3(sql);
            if (BillType == "includeDraw")//订单包含下发图纸数量
            {  sql = @"select a.projectID,projectNo,drawCount,adjCount,b.PSTime,b.PETime,b.RSTime,b.RETime 
from tbDrawMgr a
left join tbMgrNodeInfo b on a.projectID=b.projectID 
where a.drawCount>0 and 
b.PSTime is not null ";
                DataTable dt_draw = SqlHelper.GetDataTableIE(sql);
                int[] draws=new int[13];
                foreach (DataRow item in dt_draw.Rows)
                {
                    DateTime datePS = Convert.ToDateTime(item["PSTime"]);
                    int year = datePS.Year;
                    if (year==yearID)
                    {
                       int month = datePS.Month;
                       int count = Convert.ToInt32(item["drawCount"]) + Convert.ToInt32(item["adjCount"]);
                       draws[month] += count;
                       draws[0] += count;
                        //billcount
                    } 
                }
                for (int i = 0; i < 13; i++)
                {
                    //dt.Rows[i]["billcount"] = Convert.ToInt32(dt.Rows[i]["billcount"]) + draws[i];
                    dt.Rows[i]["billcount"] =  draws[i];
                   // int billcount = Convert.ToInt32(dt.Rows[i]["billcount"]);
                    if (draws[i] > 0)
                    {
                        dt.Rows[i]["rate"] = Convert.ToDouble(dt.Rows[i]["finished"]) / draws[i]; 
                    } 
                }
            }
            return dt;
    }


        //获取公司年度考核生产任务单详情表，按销售单日期或计划完工日期两种方式取订单
        //jhsl计划数量，ddlx订单类型，rksl入库数量，rksj入库时间，jhsj交货时间，xdsj下达时间，wgsj完工时间，jhwgsj计划完工时间，tzsj图纸时间
        //ddlx：订单类型68加急 55普通 57 备件计划 1004 研发计划 1002 维修，其中68排除
        public static DataTable yearCheckICMO(int yearID = 2020) {
          string sql = @"select  a.FBillNo,a.FQty jhsl,a.FWorkTypeID ddlx,b.FQty rksl,c.FDate rksj,
e.FAdviceConsignDate jhsj,a.fcommitdate xdsj,a.ffinishdate wgsj,
a.FPlanFinishDate jhwgsj,a.FItemID,FHeadSelfJ0181 dept,f.FName,f.FModel,g.FCreateDate tzsj
from ICMO a
left join  ICStockBillEntry b on a.fitemID=b.FitemID and a.FbillNo=b.FSourceBillNo
left join ICStockBill c on c.FInterID=b.FInterID
left join  Seorder d on a.FbillNo=d.FbillNo
left join SeorderEntry e on e.FInterID=d.FInterID and e.FItemID=a.FItemID
left join t_ICITEMCORE f on f.FItemID=a.FItemID
left join t_baseProperty g on g.FItemID=a.FItemID
where a.FWorkTypeID !=68 and
(e.FAdviceConsignDate between '{0}-1-1' and '{1}-1-1' or a.FPlanFinishDate between '{0}-1-1' and '{1}-1-1')
and (c.FTranType='2' or c.FTranType is null) 
order by a.FBillNo  asc";
            sql = string.Format(sql, yearID, yearID + 1); 
            return SqlHelper.GetDataTableK3(sql); 
        }

        /// <summary>
        /// 将生产任务单注册到考核日期，规则：有对应销售订单的生产任务单及其子单按交货期考核，无对应销售订单的按计划完工日期考核
        /// </summary>
        /// <param name="dts"></param>
        /// <returns></returns>
        public static DataTable yearCheckICMOregDate(DataTable dts) {
            dts.Columns.Add("checkDate",typeof(DateTime));
            string mainFBillNo = "";
            DateTime checkDate = new DateTime();
            char[] testchar={'-','_'};
            for (int i = 0; i < dts.Rows.Count; i++)
            { 
                object jhsj = dts.Rows[i]["jhsj"];
                string billno = dts.Rows[i]["FBillNo"].ToString();
                if (jhsj==DBNull.Value) //1、优先检查是否有交货时间
                {
                    if (mainFBillNo!="" && billno.StartsWith(mainFBillNo) && billno.IndexOfAny(testchar) > 0)//编码起始包含主单，且包含子单识别码，则认为是子单；
                    {
                         dts.Rows[i]["checkDate"] = checkDate;
                         //System.Diagnostics.Debug.WriteLine("当前单号:{0}，判定具有主单，主单号为{1}", billno, mainFBillNo);
                    }else 
                    {
                        dts.Rows[i]["checkDate"] = dts.Rows[i]["jhwgsj"];//取计划完工时间；
                    } 

                }
                else
                {
                    dts.Rows[i]["checkDate"] = dts.Rows[i]["jhsj"];//取交货时间；
                    checkDate = (DateTime)dts.Rows[i]["jhsj"];
                    mainFBillNo = billno;//更新当前主单编号 
                }
            }
            return dts;
        }


        public static DataTable finishRateDetail(string dept, int yearID, string itemName, string FBillNo, string FModel, string period, string sort, int pagesize, int pageindex, string strWhere, out int totalCount)
        {
            DataTable dts_raw = yearCheckICMO(yearID);
            DataTable dts = yearCheckICMOregDate(dts_raw);//校正考核完工日期
            totalCount=dts.Rows.Count;
            string select = "1=1";
            if (dept.Length>1)
            {
                select += string.Format(" and dept='{0}'", dept);
            }
            if (itemName.Length>1)
            {
                 select += string.Format(" and FName like '%{0}%'", itemName);
            }
            if (FBillNo.Length > 1)
            {
                select += string.Format(" and FBillNo like '{0}%'", FBillNo);
            }
            if (FModel.Length > 1)
            {
                select += string.Format(" and FModel like '{0}%'", FModel);
            }
            DataTable dtout = DataTableHelper.SelectRange(dts, (pageindex - 1) * pagesize+1, pageindex * pagesize, "", select);
           return dtout;
        
        }
        //公司生产月兑现率统计报表
        public static DataTable MonthMOFinishRate(int yearID = 2020) {

            DataTable dts_raw = yearCheckICMO(yearID);
            DataTable dts0 = yearCheckICMOregDate(dts_raw);//校正考核完工日期
            string selectstr=string.Format("checkDate >= '{0}-1-1' and checkDate < '{1}-1-1'",yearID,yearID+1); //再次矫正数据范围
            DataTable dts = DataTableHelper.SelectRange(dts0, 1, dts0.Rows.Count, "", selectstr);

            string[] month = { "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月" ,"合计"};
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("month",typeof(string)));//月份
            dt.Columns.Add(new DataColumn("orderBillCount", typeof(Int32)));//事业部下达生产任务数
            dt.Columns.Add(new DataColumn("onTimeFinishCount", typeof(Int32)));//制造部按计划完成任务数
            dt.Columns.Add(new DataColumn("allFinishCount", typeof(Int32)));//制造完成数
            dt.Columns.Add(new DataColumn("disCheckCount", typeof(Int32)));//制造部申请免考核数
            dt.Columns.Add(new DataColumn("jhdxl", typeof(double)));//计划兑现率
            dt.Columns.Add(new DataColumn("khjhdxl", typeof(double)));//考核计划兑现率
            dt.Columns.Add(new DataColumn("dxl", typeof(double)));//兑现率
            //初始化表视图
            for (int i = 0; i < 13; i++)
            {
                DataRow r = dt.NewRow();
                r[0] = month[i];
                for (int j = 1; j < 8; j++)
                {
                     r[j] = 0;
                } 
                dt.Rows.Add(r);
            } 
                //获得去重后得任务单,一个任务单可能对应多条入库单；
                DataView dv=new DataView(dts);
                DataTable dtsd = dv.ToTable(true, "FBillNo", "jhsl", "checkDate");
                foreach (DataRow item in dtsd.Rows)
                {
                    DateTime acDate = Convert.ToDateTime(item["checkDate"]);
                    int acMonth = acDate.Month;
                    dt.Rows[acMonth - 1][1] = Convert.ToInt32(dt.Rows[acMonth - 1][1]) + Convert.ToInt32(item["jhsl"]);//累计下达数
                } 

            foreach (DataRow item in dts.Rows)
            {
                int rksl = item["rksl"] == DBNull.Value ? 0 : Convert.ToInt32(item["rksl"]);
                if (rksl==0)
                {
                    continue;
                }
                DateTime acDate = Convert.ToDateTime(item["checkDate"]);
                DateTime rkDate = Convert.ToDateTime(item["rksj"]);
                int acMonth = acDate.Month;
                if (rkDate<=acDate)//按时完成
                {
                    dt.Rows[acMonth - 1][2] = Convert.ToInt32(dt.Rows[acMonth - 1][2]) + rksl;//累计按计划完成任务数 
                }
                dt.Rows[acMonth - 1][3] = Convert.ToInt32(dt.Rows[acMonth - 1][3]) + rksl;//累计完成任务数 
            }
          
                //取制造部申请免考核数量
                DataTable dtdischeck = SqlHelper.GetDataTableIE("select * from tbDisCheckOrder where type='disCheckCount' and year=" + yearID);
                DataRow dwdischeck = dtdischeck.Rows[0];
                for (int i = 0; i < 12; i++)
                {
                    dt.Rows[i][4] = dwdischeck[i + 2] == DBNull.Value ? 0 : Convert.ToInt32(dwdischeck[i + 2]); //添加免考核数据 
                }
                //添加汇总行； 
                  for (int i = 0; i < 12; i++)
                  {
                      dt.Rows[12][1] = Convert.ToInt32(dt.Rows[12][1]) + Convert.ToInt32(dt.Rows[i][1]);
                      dt.Rows[12][2] = Convert.ToInt32(dt.Rows[12][2]) + Convert.ToInt32(dt.Rows[i][2]);
                      dt.Rows[12][3] = Convert.ToInt32(dt.Rows[12][3]) + Convert.ToInt32(dt.Rows[i][3]);
                      dt.Rows[12][4] = Convert.ToInt32(dt.Rows[12][4]) + Convert.ToInt32(dt.Rows[i][4]);
                  }
                foreach (DataRow item in dt.Rows)
                {
                    //计算兑现率
                    double a = Convert.ToDouble(item[1]);//下达任务数
                    double b = Convert.ToDouble(item[2]);//按计划完成任务数；
                    double c = Convert.ToDouble(item[3]);//完成数； 
                    double d = item[4] == DBNull.Value ? 0 : Convert.ToDouble(item[4]);//免考核数；
                    item[5] = (a==0?0:(b/a));//计划兑现率
                    item[6] = ((c - d) <= 0 ? 0 : ((b - d) / (c - d)));//考核计划兑现率
                    item[7] = (a == 0 ? 0 : (c / a));//兑现率
                }

            return dt;

        }


        
        /// <summary>
        /// 首道工序未开工主单查询--开工预警，钻取单据
        /// </summary>
        /// <param name="startPage"></param>
        /// <param name="endPage"></param>
        /// <param name="where"></param>
        /// <param name="totalCount"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public static DataTable GetUnStartProjectDataTablePager(int pageindex, int pagesize, string dept, out int totalCount, string order = "FPlanCommitDate desc")
        {
            string tables="ICMO a left join t_ICITEMCORE b on a.FItemID=b.FItemID ";
            string innercolums="FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,a.FItemID as FItemID,FName,FModel,FInterID,fheadselfj0181,FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid ";
            string  outercolums="FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,FItemID,FName,FModel,FInterID,fheadselfj0181,FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid ";

            string wheres=" a.FStatus<3 and a.FBillNo in(select distinct FEntrySelfz0373 from shworkbillentry c where c.S='Y') ";
            switch (dept)
	                {
                                case   "其他":
                                    wheres+=" and fheadselfj0181 is null ";
                                    break;
                                case "all":
                                case "所有":
                                case "汇总":
                                    break;
		                        default:
                                    wheres+=string.Format( " and fheadselfj0181 in ('{0}') ",dept);
                                     break;
	                }
            DataTable dt = SqlPagerHelper.GetPagerBySQL(SqlHelper.connStrK3,tables,outercolums,innercolums,pagesize,pageindex,wheres,order,out totalCount);    
            return dt;
        }


        /// <summary>
        /// 直接使用sql获取生产任务单的分页查询，用于替代存储过程,可返回所有单据
        /// </summary>
        /// <param name="startPage"></param>
        /// <param name="endPage"></param>
        /// <param name="where"></param>
        /// <param name="totalCount"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public static DataTable GetProjectDataTablePager(int startPage, int endPage, string where, out int totalCount, string order = "FPlanCommitDate desc")
        {
            StringBuilder sbsql = new StringBuilder();
            sbsql.Append("select  FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,FItemID,FName,FModel,FInterID,fheadselfj0181,FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid from (");
            sbsql.AppendFormat("select row_number() over(order by {0})", order);
            sbsql.Append(" as Rownum,FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,ICMO.FItemID as FItemID,FName,FModel,FInterID,fheadselfj0181,FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid ");
            sbsql.Append(" from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID  ");//连接数据表t_ICITEMCORE为实现物料名称查询；
            sbsql.AppendFormat(" where {0}", where);
            sbsql.AppendFormat(") as T where T.Rownum between  {0}  and  {1}", startPage, endPage);

            DataTable dt = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sbsql.ToString(), null);
            StringBuilder sbtotalsql = new StringBuilder();
            sbtotalsql.AppendFormat(" select  count(*) from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID where {0}", where);
            totalCount = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sbtotalsql.ToString()));//select @@rowcount 上一查询的数据

            dt.Columns.Add(new DataColumn("FStockQty"));//增加库存数量字段
            dt.Columns.Add(new DataColumn("ppbomstatus"));//增加是否领料字段
            dt.Columns.Add(new DataColumn("hasChild", typeof(bool)));//增加是否存在子单字段
            dt.Columns.Add(new DataColumn("overdue", typeof(bool)));//增加是否逾期字段
            dt.Columns.Add(new DataColumn("notStart", typeof(bool)));//增加是否未开工字段
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //DataTable dticitemcore = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                //dt.Rows[i]["FStockQty"] = dticitemcore.Rows[0][0].GetType() ==typeof( DBNull) ? 0 : Convert.ToSingle(dticitemcore.Rows[0][0]);
                object o = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                dt.Rows[i]["FStockQty"] = o is DBNull ? 0 : Convert.ToInt32(o);
                dt.Rows[i]["ppbomstatus"] = GetppbomstatusByFBillNo(dt.Rows[i]["FBillNo"].ToString());
                dt.Rows[i]["hasChild"] = hasChild(dt.Rows[i]["FBillNo"].ToString());//添加是否含有子单
                dt.Rows[i]["overdue"] = overdue(dt.Rows[i]["FBillNo"].ToString());//添加是否逾期
                dt.Rows[i]["notStart"] = NotStarted(dt.Rows[i]["FBillNo"].ToString());//添加是否未开工
            }
            //将逾期的订单变更为加急订单！
            foreach (DataRow item in dt.Rows)
            {
                if (Convert.ToBoolean(item["overdue"]) && Convert.ToInt32(item["FWORKTYPEID"]) != 68)//逾期且订单类型不是加急
                {
                    //将预期订单全部自动设置为加急订单
                    try
                    {
                        string sql_jj = string.Format("update ICMO set FWORKTYPEID = 68 where FBillNo='{0}'", Convert.ToString(item["FBillNo"]));
                        SqlHelper.ExecuteNonQuerySql(SqlHelper.connStrK3, sql_jj);
                    }
                    catch (Exception)
                    {
                        //throw;
                    }
                }

            }

            return dt;
        }
        /// <summary>
        /// 获取所有未完成生产任务单主单，散单自动添加虚拟主单进行聚类,fitemid==0为虚拟主单，
        /// </summary>
        /// <returns></returns>
        public static DataTable GetUnClosedMainProject(string strWhere, string order = " FPlanFinishDate asc")
        {
            string sql = @"select FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,
                                 fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,
                                 FStartDate,FFinishDate,FType,FWorkShop,
                                FItemID,FName,FModel,FInterID,fheadselfj0181,
                                 FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid from
                                 (
                                 select  FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,
                                 fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,
                                 FStartDate,FFinishDate,FType,FWorkShop,
                                 ICMO.FItemID as FItemID,FName,FModel,FInterID,fheadselfj0181,
                                 FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid 
                                 from ICMO WITH(NOLOCK) left join t_ICITEMCORE WITH(NOLOCK) on ICMO.FItemID=t_ICITEMCORE.FItemID 
                                 where FBillNo not like '%[_-]%' and (fstatus <3)
                                 union
                                 select 0 FWORKTYPEID,left(FBillNo,charindex('_', FBillNo)-1) as FBillNo,max(FHEADSELFJ0178),min(FStatus),min(fmrpclosed),0 FQty,0 FCommitQty,
                                     0 fauxstockqty,min(FPlanCommitDate),max(FPlanFinishDate),min(FCheckDate),
                                     min( FStartDate), min(FFinishDate), min(FType), min(FWorkShop),
                                      0 FItemID,'--' FName, '--' FModel,0 FInterID,max(fheadselfj0181),
                                     0 FHEADSELFJ0182,0 FHEADSELFJ0183,'--' fheadselfj0186, 0 fbominterid 
	                                from icmo WITH(NOLOCK) left join t_ICITEMCORE WITH(NOLOCK) on ICMO.FItemID=t_ICITEMCORE.FItemID 
	                                where FBillNo like  '%[_]%' and (fstatus <3)
		                                and left(FBillNo,charindex('_', FBillNo)-1)  not in 
		                                (select FBillNo  from ICMO WITH(NOLOCK) where  Fbillno not like '%[_-]%')  
		                                group by left(FBillNo,charindex('_', FBillNo)-1)
									union
                                select 0 FWORKTYPEID,left(FBillNo,charindex('-', FBillNo)-1) as FBillNo,max(FHEADSELFJ0178),min(FStatus),min(fmrpclosed),0 FQty,0 FCommitQty,
                                     0 fauxstockqty,min(FPlanCommitDate),max(FPlanFinishDate),min(FCheckDate),
                                     min( FStartDate), min(FFinishDate), min(FType), min(FWorkShop),
                                      0 FItemID,'--' FName, '--' FModel,0 FInterID,max(fheadselfj0181),
                                     0 FHEADSELFJ0182,0 FHEADSELFJ0183,'--' fheadselfj0186, 0 fbominterid 
	                                from icmo WITH(NOLOCK) left join t_ICITEMCORE WITH(NOLOCK)  on ICMO.FItemID=t_ICITEMCORE.FItemID 
	                                where FBillNo like  '%[-]%' and (fstatus <3)
		                                and left(FBillNo,charindex('-', FBillNo)-1)  not in 
		                                (select FBillNo  from ICMO WITH(NOLOCK) where  Fbillno not like '%[_-]%')  
		                                group by left(FBillNo,charindex('-', FBillNo)-1)
                                ) as t ";
            sql += string.Format(" where {0} order by {1}", strWhere, order); //增加过滤和排序条件；
            return SqlHelper.GetDataTableK3(sql);
        }

        /// <summary>
        /// 获取所有生产任务单主单，散单自动添加虚拟主单进行聚类,，fitemid==0为虚拟主单，
        /// 可能产生异常：事务(进程 ID 67)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。
        /// 2019.8.19，对表使用无锁读取： WITH(NOLOCK)，日后查看效果 
        /// </summary>
        /// <returns></returns>
        public static DataTable GetMainProject(string strWhere, string order = "FPlanFinishDate desc")
        {
            string sql = @"select FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,
                                 fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,
                                 FStartDate,FFinishDate,FType,FWorkShop,
                                FItemID,FName,FModel,FInterID,fheadselfj0181,
                                 FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid from
                                 (
                                 select  FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,
                                 fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,
                                 FStartDate,FFinishDate,FType,FWorkShop,
                                 ICMO.FItemID as FItemID,FName,FModel,FInterID,fheadselfj0181,
                                 FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid 
                                 from ICMO  WITH(NOLOCK) left join t_ICITEMCORE  WITH(NOLOCK) on ICMO.FItemID=t_ICITEMCORE.FItemID 
                                 where FBillNo not like '%[_-]%' 
                                 union
                                 select 0 FWORKTYPEID,left(FBillNo,charindex('_', FBillNo)-1) as FBillNo,max(FHEADSELFJ0178),min(FStatus),min(fmrpclosed),0 FQty,0 FCommitQty,
                                     0 fauxstockqty,min(FPlanCommitDate),max(FPlanFinishDate),min(FCheckDate),
                                     min( FStartDate), min(FFinishDate), min(FType), min(FWorkShop),
                                      0 FItemID,'--' FName, '--' FModel,0 FInterID,max(fheadselfj0181),
                                     0 FHEADSELFJ0182,0 FHEADSELFJ0183,'--' fheadselfj0186, 0 fbominterid 
	                                from icmo  WITH(NOLOCK) left join t_ICITEMCORE  WITH(NOLOCK) on ICMO.FItemID=t_ICITEMCORE.FItemID 
	                                where FBillNo like  '%[_]%'
		                                and left(FBillNo,charindex('_', FBillNo)-1)  not in 
		                                (select FBillNo  from ICMO WITH(NOLOCK) where  Fbillno not like '%[_-]%')  
		                                group by left(FBillNo,charindex('_', FBillNo)-1)
									union
                                select 0 FWORKTYPEID,left(FBillNo,charindex('-', FBillNo)-1) as FBillNo,max(FHEADSELFJ0178),min(FStatus),min(fmrpclosed),0 FQty,0 FCommitQty,
                                     0 fauxstockqty,min(FPlanCommitDate),max(FPlanFinishDate),min(FCheckDate),
                                     min( FStartDate), min(FFinishDate), min(FType), min(FWorkShop),
                                      0 FItemID,'--' FName, '--' FModel,0 FInterID,max(fheadselfj0181),
                                     0 FHEADSELFJ0182,0 FHEADSELFJ0183,'--' fheadselfj0186, 0 fbominterid 
	                                from icmo  WITH(NOLOCK) left join t_ICITEMCORE  WITH(NOLOCK) on ICMO.FItemID=t_ICITEMCORE.FItemID 
	                                where FBillNo like  '%[-]%'
		                                and left(FBillNo,charindex('-', FBillNo)-1)  not in 
		                                (select FBillNo  from ICMO  WITH(NOLOCK) where  Fbillno not like '%[_-]%')  
		                                group by left(FBillNo,charindex('-', FBillNo)-1)
                                ) as t";
            sql += string.Format(" where {0} order by {1}", strWhere, order); //增加过滤和排序条件；
            return SqlHelper.GetDataTableK3(sql);
        }

        /// <summary>
        /// 主生产任务单的分页查询（含虚拟主单）
        /// </summary>
        /// <param name="startPage"></param>
        /// <param name="endPage"></param>
        /// <param name="where"></param>
        /// <param name="totalCount"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public static DataTable GetPageMainProject(int startPage, int endPage, string strWhere, out int totalCount, string order = " FPlanFinishDate asc")
        {
            DataTable sourceDt = GetMainProject(strWhere, order);
            totalCount = sourceDt.Rows.Count;
            DataTable dt = AchieveCommon.DataTableHelper.SelectRange(sourceDt, startPage, endPage);
            dt.Columns.Add(new DataColumn("FStockQty"));//增加库存数量字段
            dt.Columns.Add(new DataColumn("ppbomstatus"));//增加是否领料字段
            dt.Columns.Add(new DataColumn("hasChild", typeof(bool)));//增加是否存在子单字段
            dt.Columns.Add(new DataColumn("overdue", typeof(bool)));//增加是否逾期字段
            dt.Columns.Add(new DataColumn("notStart", typeof(bool)));//增加是否未开工字段

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //DataTable dticitemcore = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                //dt.Rows[i]["FStockQty"] = dticitemcore.Rows[0][0].GetType() ==typeof( DBNull) ? 0 : Convert.ToSingle(dticitemcore.Rows[0][0]);
                if (Convert.ToInt32(dt.Rows[i]["FItemID"]) > 0)//真实主单
                {
                    object o = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                    dt.Rows[i]["FStockQty"] = o is DBNull ? 0 : Convert.ToInt32(o);
                    dt.Rows[i]["ppbomstatus"] = GetppbomstatusByFBillNo(dt.Rows[i]["FBillNo"].ToString());
                    dt.Rows[i]["hasChild"] = hasChild(dt.Rows[i]["FBillNo"].ToString());//添加是否含有子单
                    dt.Rows[i]["overdue"] = overdue(dt.Rows[i]["FBillNo"].ToString());//添加是否逾期
                    dt.Rows[i]["notStart"] = NotStarted(dt.Rows[i]["FBillNo"].ToString());//添加是否未开工
                }
                else//虚拟主单
                {
                    dt.Rows[i]["hasChild"] = true;
                    dt.Rows[i]["overdue"] = overdue(dt.Rows[i]["FBillNo"].ToString());//添加是否逾期
                    dt.Rows[i]["notStart"] = NotStarted(dt.Rows[i]["FBillNo"].ToString());//添加是否未开工
                }

            }
            return dt;
        }

        /// <summary>
        /// 未完工主生产任务单的分页查询（含虚拟主单）
        /// </summary>
        /// <param name="startPage"></param>
        /// <param name="endPage"></param>
        /// <param name="where"></param>
        /// <param name="totalCount"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public static DataTable GetPageUnClosedMainProject(int startPage, int endPage, string strWhere, out int totalCount, string order = " FPlanFinishDate asc")
        {
            DataTable sourceDt = GetUnClosedMainProject(strWhere, order);
            totalCount = sourceDt.Rows.Count;
            DataTable dt = AchieveCommon.DataTableHelper.SelectRange(sourceDt, startPage, endPage);
            dt.Columns.Add(new DataColumn("FStockQty"));//增加库存数量字段
            dt.Columns.Add(new DataColumn("ppbomstatus"));//增加是否领料字段
            dt.Columns.Add(new DataColumn("hasChild", typeof(bool)));//增加是否存在子单字段
            dt.Columns.Add(new DataColumn("overdue", typeof(bool)));//增加是否逾期字段
            dt.Columns.Add(new DataColumn("notStart", typeof(bool)));//增加是否未开工字段

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //DataTable dticitemcore = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                //dt.Rows[i]["FStockQty"] = dticitemcore.Rows[0][0].GetType() ==typeof( DBNull) ? 0 : Convert.ToSingle(dticitemcore.Rows[0][0]);
                if (Convert.ToInt32(dt.Rows[i]["FItemID"]) > 0)//真实主单
                {
                    object o = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                    dt.Rows[i]["FStockQty"] = o is DBNull ? 0 : Convert.ToInt32(o);
                    dt.Rows[i]["ppbomstatus"] = GetppbomstatusByFBillNo(dt.Rows[i]["FBillNo"].ToString());
                    dt.Rows[i]["hasChild"] = hasChild(dt.Rows[i]["FBillNo"].ToString());//添加是否含有子单
                    dt.Rows[i]["overdue"] = overdue(dt.Rows[i]["FBillNo"].ToString());//添加是否逾期
                    dt.Rows[i]["notStart"] = NotStarted(dt.Rows[i]["FBillNo"].ToString());//添加是否未开工
                }
                else//虚拟主单
                {
                    dt.Rows[i]["hasChild"] = true;
                    dt.Rows[i]["overdue"] = overdue(dt.Rows[i]["FBillNo"].ToString());//添加是否逾期
                    dt.Rows[i]["notStart"] = NotStarted(dt.Rows[i]["FBillNo"].ToString());//添加是否未开工
                }


                //将逾期的订单变更为加急订单！

                if (Convert.ToBoolean(dt.Rows[i]["overdue"]) && Convert.ToInt32(dt.Rows[i]["FWORKTYPEID"]) != 68)//逾期且订单类型不是加急
                    {
                        //将预期订单全部自动设置为加急订单
                        try
                        {
                            string sql_jj = string.Format("update ICMO set FWORKTYPEID = 68 where FBillNo='{0}'", Convert.ToString(dt.Rows[i]["FBillNo"]));
                            SqlHelper.ExecuteNonQuerySql(SqlHelper.connStrK3, sql_jj);
                        }
                        catch (Exception)
                        {
                            //throw;
                        }
                    } 

            }
            return dt;
        }

        public static DataTable GetNotStartProjectDataTablePager(int startPage, int endPage, string where, out int totalCount, string order = "FPlanCommitDate desc")
        {
            StringBuilder sbsql = new StringBuilder();
            sbsql.Append("select  FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,FItemID,FName,FModel,FInterID,fheadselfj0181,FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid from (");
            sbsql.AppendFormat("select row_number() over(order by {0})", order);
            sbsql.Append(" as Rownum,FWORKTYPEID,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,ICMO.FItemID as FItemID,FName,FModel,FInterID,fheadselfj0181,FHEADSELFJ0182,FHEADSELFJ0183,fheadselfj0186,fbominterid ");
            sbsql.Append(" from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID  ");//连接数据表t_ICITEMCORE为实现物料名称查询；
            sbsql.AppendFormat(" where {0}", where);
            sbsql.AppendFormat(") as T where T.Rownum between  {0}  and  {1}", 0, 10000);

            DataTable dt = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sbsql.ToString(), null);//查询主表 
            //附加信息
            dt.Columns.Add(new DataColumn("FStockQty"));//增加库存数量字段
            dt.Columns.Add(new DataColumn("ppbomstatus"));//增加是否领料字段
            dt.Columns.Add(new DataColumn("hasChild", typeof(bool)));//增加是否存在子单字段
            dt.Columns.Add(new DataColumn("overdue", typeof(bool)));//增加是否逾期字段
            dt.Columns.Add(new DataColumn("notStart", typeof(bool)));//增加是否未开工字段
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                object o = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                dt.Rows[i]["FStockQty"] = o is DBNull ? 0 : Convert.ToInt32(o);
                dt.Rows[i]["ppbomstatus"] = GetppbomstatusByFBillNo(dt.Rows[i]["FBillNo"].ToString());
                dt.Rows[i]["hasChild"] = hasChild(dt.Rows[i]["FBillNo"].ToString());//添加是否含有子单
                dt.Rows[i]["overdue"] = overdue(dt.Rows[i]["FBillNo"].ToString());//添加是否逾期
                dt.Rows[i]["notStart"] = NotStarted(dt.Rows[i]["FBillNo"].ToString());//添加是否未开工
            }
            DataTable dtnotstarted = dt.Clone();
            foreach (DataRow item in dt.Rows)
            {
                if (Convert.ToBoolean(item["notStart"]))
                {
                    dtnotstarted.ImportRow(item);
                }
            }
            totalCount = dtnotstarted.Rows.Count;//总数；
            // int startPage, int endPage
            return AchieveCommon.DataTableHelper.SelectRange(dtnotstarted, startPage, endPage);

        }

        /// <summary>
        /// 主单号推测方法
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <returns></returns>
        public static string getMainBill(string FBillNo)
        {
            //将FbillNo规范为顶级订单，即主订单格式，按最后一个-_符号截取
            char[] key = { '-', '_' };
            int index = FBillNo.IndexOfAny(key);
            string mainFBillNo = string.Empty; ;//主单号，根据规则推导，未必正确，
            if (index < 1)
            {
                mainFBillNo = FBillNo;
            }
            else
            {
                mainFBillNo = FBillNo.Substring(0, index);
            }
            return mainFBillNo;
        }
      
       
  

        /// <summary>
        /// 根据主单查找子单，分页输出；
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <param name="where"></param>
        /// <param name="subClass"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalcount"></param>
        /// <returns></returns>
        public static DataTable GetPageManufactureSubOrder(string FBillNo, string where, int subClass, int pageindex, int pagesize, out int totalcount)
        {
            DataTable sdt = GetManufactureSubOrder(FBillNo, where, subClass);
            totalcount = sdt.Rows.Count;
            int FRow = (pageindex - 1) * pagesize + 1;
            int ERow = pageindex * pagesize; 
            DataTable dt = DataTableHelper.SelectRange(sdt, FRow, ERow," hasChild desc");
            dt.Columns.Add(new DataColumn("FStockQty", typeof(Int32)));//增加库存数量字段
            dt.Columns.Add(new DataColumn("ppbomstatus", typeof(bool)));//增加是否领料字段 
            dt.Columns.Add(new DataColumn("overdue", typeof(bool)));//增加是否逾期字段 
            dt.Columns.Add(new DataColumn("notStart", typeof(bool)));//增加是否领料字段            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //DataTable dticitemcore = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                //dt.Rows[i]["FStockQty"] = dticitemcore.Rows[0][0].GetType() ==typeof( DBNull) ? 0 : Convert.ToSingle(dticitemcore.Rows[0][0]);
                object o = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
                dt.Rows[i]["FStockQty"] = o is DBNull ? 0 : Convert.ToInt32(o);
                dt.Rows[i]["ppbomstatus"] = GetppbomstatusByFBillNo(dt.Rows[i]["FBillNo"].ToString());
                dt.Rows[i]["overdue"] = overdue(dt.Rows[i]["FBillNo"].ToString(), 1);//添加是否逾期
                dt.Rows[i]["notStart"] = NotStarted(dt.Rows[i]["FBillNo"].ToString());//添加是否未开工
            }
            return dt;
        }
        /// <summary>
        /// 根据Bom表将一批生产任务单划分层级
        /// </summary>
        /// <param name="mFBillNo"></param>
        /// <param name="orderDt"></param>
        /// <param name="bomDt"></param>
        /// <returns></returns>
        public static DataTable classManufactureOrderByBom(string mFBillNo, DataTable orderDt, DataTable bomDt)
        {
            // FBillNo,fbominterid,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,");
            //sbsql.Append("FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,ICMO.FItemID as FItemID,FName,FModel,FInterID,FHEADSELFJ0182,FHEADSELFJ0183 ");
            //sbsql.Append("from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID where " + strWhere);
            //orderDt.Columns.Add("class", typeof(int));           // orderDt.Columns["class"].DefaultValue = 1;
            //orderDt.Columns.Add("pmo", typeof(string));            //orderDt.Columns["pmo"].DefaultValue = ""; 
            foreach (DataRow item in orderDt.Rows)
            {
                //搜索是否存在于bom中，如无，则纳入1层；否则进入循环找出父级
                int FItemID = item["FItemID"] is DBNull ? 0 : Convert.ToInt32(item["FItemID"]); 
                   DataRow[] bomdr = bomDt.Select("FItemID=" + FItemID); //对于每条任务单，首先查询bom中有无该物料，若无，则为第1层，若有，继续找父级；
              if (bomdr.Length == 0)//无此物料，纳入1层
                   {
                       item["class"] = 1;
                       item["pmo"] = mFBillNo;//记录父级单据
                       continue;
                       //  up = false;//结束循环； 
                   }
                   else
                   {
                       int BomLevel =Convert.ToInt32(bomdr[0]["BomLevel"]);
                       if (BomLevel==1)
                           {
                               item["class"] = 1;
                               item["pmo"] = mFBillNo;//记录父级单据
                               continue;
                           }
                   }
                 //其他情况进入循环判断，取bom表中父级数据参考
                   int pFItemID = Convert.ToInt32(bomdr[0]["FFItemID"]);//bom中父物料id                   
                   string pmo = mFBillNo;
                while (true)
                {
                    //找父级
                    DataRow[] orderdr0 = orderDt.Select("FItemID=" + pFItemID);   //查找父订单；
                    DataRow[] bomdr0 = bomDt.Select("FItemID=" + pFItemID);   //查找父物料对应的Bom，不一定存在！
                    int Level=1;
                    if (bomdr0.Length>0)
                    {
                        Level = Convert.ToInt32(bomdr0[0]["BomLevel"]);
                    }
                    
                    if (orderdr0.Length>0)//存在对应父级
                    {
                        //item["class"] = 1;
                        pmo = orderdr0[0]["FBillNo"].ToString();
                        item["pmo"] = pmo;//记录父级单据
                        orderdr0[0]["hasChild"] = 1;
                        break;
                    }
                    else if (Level==1)//父级物料对应订单不存在，但父级物料bom为第一级，记录上级为主单
	                        {
                                item["class"] = 1;
                                item["pmo"] = mFBillNo;//记录父级单据
                                break;
	                        }
             else//跳级
                    { 
                        pFItemID = Convert.ToInt32(bomdr0[0]["FFItemID"]);//bom中父物料id 
                    }
                    
                    
                } 
 
            }
            return orderDt;
        }

        /// <summary>
        /// 取子孙任务单的递归函数
        /// </summary>
        /// <param name="orders">原始数据集</param>
        /// <param name="Fbillno">当前父单号</param>
        /// <param name="outOrders">输出数据集</param>
        private static void getSubOrder(DataTable orders,string Fbillno,ref DataTable outOrders){
            DataRow[] dr = orders.Select("pmo='" + Fbillno+"'");
            if (dr.Length > 0)
            { 
                for (int i = 0; i < dr.Length; i++)
                {
                    outOrders.Rows.Add(dr[i].ItemArray);
                }
                foreach (DataRow item in dr)
                {
                    if (Convert.ToInt32(item["hasChild"]) == 1)
                    {
                        getSubOrder(orders, item["FBillNo"].ToString(), ref outOrders);
                    }
                    else { return; }
                }

            }
            else { return; }
        
        }

 

        /// <summary>
        /// 根据主生产任务单号，bom分级号，查询条件获取子生产计划表
        /// </summary>
        /// <param name="FBillNo">主单单号</param>
        /// <param name="subClass">当前子表分级号,0表示返回下级所有任务单，不再分层，1按层级返回当层数据,2.3.4...均以当前单据为父级，返回下一级的单据</param> 
        /// <returns></returns>
        public static DataTable GetManufactureSubOrder(string FBillNo, string strWhere, int subClass)
        {
            ////将FbillNo规范为顶级订单，即主订单格式，按最后一个-_符号截取
            string mainFBillNo = getMainBill(FBillNo);
            //char[] key = { '-', '_' };
            //int index = FBillNo.IndexOfAny(key);
            //string mainFBillNo;//主单号，根据规则推导，未必正确，
            //if (index < 1)
            //{
            //    mainFBillNo = FBillNo;
            //}
            //else
            //{
            //    mainFBillNo = FBillNo.Substring(0, index);
            //}
            int fbominterid = GetFBomidByFBillNO(mainFBillNo);//获取顶级Bomid
            if (string.IsNullOrWhiteSpace(strWhere))
            {
                strWhere = string.Format(" FBillNo like '{0}[_-]%'", mainFBillNo);
            }
            else
            {
                strWhere += string.Format(" and FBillNo like '{0}[_-]%'", mainFBillNo);
            }
            StringBuilder sbsql = new StringBuilder();
            sbsql.Append("select FWORKTYPEID,FBillNo,fbominterid,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,");
            sbsql.Append(" ICMO.FItemID as FItemID,FName,FModel,FInterID,FHEADSELFJ0182,FHEADSELFJ0183, 1 class,");
             sbsql.Append(string.Format("'{0}' pmo, 0 hasChild ", mainFBillNo));
            sbsql.Append(" from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID where " + strWhere);
            //找出相关订单；
            DataTable dticmo = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sbsql.ToString(), null);
            DataTable dt; //结果表；
            int FItemID = GetFItemIDByFBillNO(mainFBillNo);//主物料ID，不一定是主单，父单
            if (FItemID > 0)
            {
                DataTable dtBom = BomBLL.getBomTableByItemID(FItemID);
                dt = classManufactureOrderByBom(mainFBillNo, dticmo, dtBom);   ////根据bom表获取下层级订单 
            }
            else//虚拟主单；无法分层！！
            {
                dt = dticmo;
            }
            if (subClass == 0)
            {
               DataTable outdt = dt.Clone();
               getSubOrder(dt, FBillNo, ref outdt);
               return outdt;//返回所有数据 
            }         
                else if (subClass > 0)
                {
                   // return DataTableHelper.SelectTop(dt, "class=" + subClass + " and pmo='"+FBillNo+"'" , null, 0);//返回对应层级数据
                    return DataTableHelper.SelectTop(dt, "pmo='" + FBillNo + "'", null, 0);//返回对应子单数据
                }
            return dt;//返回所有数据 
        }

        /// <summary>
        /// 查询生产任务单是否逾期，用于界面提醒,mode=0 ,主单，考虑所有子单，mode=1,：仅判断本单
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <returns></returns>
        public static bool overdue(string FBillNo, int Mode = 0)
        {
            // 获取所有子单
            //检查子单是否逾期
            //子单和父单均设置逾期 
            string sql;
            if (Mode == 1)
            {
                sql = string.Format("select count(FBillNo) from icmo  where FBillNo = '{0}' and FStatus<3 and FPlanFinishDate<'{1}'", FBillNo, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            }
            else
            {
                sql = string.Format("select count(FBillNo) from icmo  where FBillNo like '{0}%' and FStatus<3 and FPlanFinishDate<'{1}'", FBillNo, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            int count = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql));
            if (count > 0)
            {
              
               
                return true;
            }
            else
            {
                return false;
            }
        }



        /// <summary>
        /// 查询生产任务单是否为--首个工单未开工，用于开工预警,返回true为未开工
        /// </summary>
        /// <param name="FBillNo">严格按照任务单号，实际多为子单</param>
        /// <returns></returns>
        public static bool NotStarted(string FBillNo)
        {
            string sql = String.Format("select  count(s) from shworkbill a right join shworkbillentry b on a.FinterID =b.FinterID where  S='Y' and ficmono='{0}'", FBillNo);
            int count = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql));
            return count > 0;
        }
        /// <summary>
        /// 查询主生产任务单是否存在子生产任务单
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <returns></returns>
        public static bool hasChild(string FBillNo)
        {
            string sql = String.Format("select count(FBillNo) from icmo where FBillNo like  '{0}[_-]%'", FBillNo);
            int count = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql));
            return count > 0;
        }

       

        //新增 20190223 访问IE数据库
        public string GetJsonFromSql(string sql)
        {
            DataTable dt = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStr, CommandType.Text, sql);
            return AchieveCommon.JsonHelper.ToJson(dt);
        }
        /// <summary>
        /// 根据物料ID查找物料名称、规格型号等信息
        /// </summary>
        /// <param name="FItemID"></param>
        /// <returns></returns>
        public DataTable GetFNameByFItemID(int FItemID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select FModel,FName from t_icitemcore");
            sb.Append(" where FItemID=@Id");
            return SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sb.ToString(), new SqlParameter("@Id", FItemID));
        }
        /// <summary>
        /// 根据物料ID查询物料库存数量
        /// </summary>
        /// <param name="FItemID"></param>
        /// <returns></returns>
        public static object GetFQTYByFItemID(int FItemID)
        {
            string sql = string.Format(" select sum(FQty) from ICINVENTORy where FItemID={0}", FItemID);
            return SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql);
            //return SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sb.ToString(), new SqlParameter("@Id", FItemID));
        }
        /// <summary>
        /// 根据单号查询是否领料
        /// </summary>
        /// <param name="FItemID"></param>
        /// <returns></returns>
        public static int GetppbomstatusByFBillNo(string FBillNo)
        {
            string sql = string.Format(" select a.fstatus from ppbom as a right join icmo as b on a.ficmointerid=b.finterid where b.fbillno='{0}'", FBillNo);
            object o = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql);
            if (o is DBNull)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(o);
            }

        }

        public static DataTable GetFItemIDByFName(string FName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select FItemID from t_icitemcore");
            sb.Append(" where FName like '%" + FName + "%'");
            return SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sb.ToString(), null);
        }
        /// <summary>
        /// 当无结果时返回0
        /// </summary>
        /// <param name="FBillNo"></param>
        /// <returns></returns>
        public static int GetFBomidByFBillNO(string FBillNo)
        {
            //string sql = string.Format("select fbominterid from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID  where fbillno='{0}'", FBillNo);//by SB
            string sql = string.Format("select fbominterid from ICMO where fbillno='{0}'", FBillNo);
            object o = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql);
            if (o == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(o);
            }
        }

        public static int GetFItemIDByFBillNO(string FBillNo)
        {
            string sql = string.Format("select FItemID from ICMO where fbillno='{0}'", FBillNo);
            object o = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql);
            if (o == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(o);
            }
        }

        


    }
}
